package sprinklerbee.db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.List;
import java.util.Properties;

public class DbConn {

	/**
	 * Connection object for the sprinklerbee system
	 */
	private Connection con = null;
	/**
	 * Statement object which will execute sql
	 */
	private Statement sta=null;
	/**
	 * Database the connection url
	 */
	private String url;
	/**
	 * User name for database
	 */
	private String username;
	/**
	 * Password for database
	 */
	private String password;

	/**
	 * Default constructor, which will initialize the configuration from database.properties
	 */
	public DbConn() {
		Properties properties = new Properties();
		String propertiesFile="/database.properties";
		try{			
			String filePath = DbConn.class.getResource(propertiesFile).getPath();
			properties.load(new FileInputStream(new File(filePath)));
			String driverName = properties.getProperty("database.driverClassName");
			url = properties.getProperty("database.url");
			username = properties.getProperty("database.username");
			password = properties.getProperty("database.password");
			Class.forName(driverName);
		}
		catch(FileNotFoundException ex){
			// TODO Auto-generated catch block
			ex.printStackTrace();
		}catch(IOException ex){
			// TODO Auto-generated catch block
			ex.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * Build connection and statement
	 */
	private void getConnection(){
		try {
			con = DriverManager.getConnection(url, username, password);
			sta=con.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Execute a retrieving SQL statement
	 * 
	 * @param sql any SQL statement
	 * @return the result list
	 */
	public ResultSet query(String sql) {
		getConnection();
		ResultSet rs = null;
		System.out.println("SQL:"+sql);
		try {
			rs = sta.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * Execute transactional SQLstatements
	 * 
	 * @param sqls list of SQL statements
	 * @return if any record is updated in the database
	 */
	public boolean executeTransaction(List<String> sqls) {
		System.out.println("SQLs:"+sqls);
		getConnection();
		boolean result=false;
		try {
			con.setAutoCommit(false);
			sta=con.createStatement();
			for (String sql : sqls) {
				sta.executeUpdate(sql);
			}
			con.commit();
			con.setAutoCommit(true);
			result=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * Execute a update SQL statement
	 * @param sql any SQL statement
	 * @return how many records are updated
	 */
	public int update(String sql) {
		System.out.println("SQL:"+sql);
		getConnection();
		int i = 0;
		try {
			//Statement sta = con.createStatement();
			i = sta.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * Close the connection with database
	 */
	public void close(){
		try {
			if (null!=sta) {
				sta.close();
			}
			if (null!=con) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
